package com.ems.dao;

import com.ems.model.Attendance;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AttendanceDAO {
    public boolean addAttendance(Attendance attendance) throws SQLException {
        String sql = "INSERT INTO attendances (emp_id, record_date, status, leave_reason) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, attendance.getEmpId() != null ? attendance.getEmpId() : "");

            if (attendance.getRecordDate() != null) {
                stmt.setDate(2, new java.sql.Date(attendance.getRecordDate().getTime()));
            } else {
                stmt.setDate(2, new java.sql.Date(System.currentTimeMillis()));
            }

            stmt.setString(3, attendance.getStatus());
            stmt.setString(4, attendance.getLeaveReason());

            return stmt.executeUpdate() > 0;
        }
    }

    public boolean updateAttendance(Attendance attendance) throws SQLException {
        String sql = "UPDATE attendances SET emp_id=?, record_date=?, status=?, leave_reason=? WHERE attendance_id=?";
        System.out.println("执行更新SQL: " + sql);
        System.out.println("参数: " + attendance);

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 设置参数 - 添加空值检查
            stmt.setString(1, attendance.getEmpId() != null ? attendance.getEmpId() : "");

            if (attendance.getRecordDate() != null) {
                stmt.setDate(2, new java.sql.Date(attendance.getRecordDate().getTime()));
            } else {
                stmt.setNull(2, java.sql.Types.DATE);
            }

            stmt.setString(3, attendance.getStatus() != null ? attendance.getStatus() : "");
            stmt.setString(4, attendance.getLeaveReason() != null ? attendance.getLeaveReason() : "");

            stmt.setInt(5, attendance.getAttendanceId());

            int rowsUpdated = stmt.executeUpdate();
            System.out.println("更新影响行数: " + rowsUpdated);

            return rowsUpdated > 0;
        }
    }

    public boolean deleteAttendance(int attendanceId) throws SQLException {
        String sql = "DELETE FROM attendances WHERE attendance_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, attendanceId);
            return stmt.executeUpdate() > 0;
        }
    }

    public Attendance getAttendanceById(int attendanceId) throws SQLException {
        String sql = "SELECT * FROM attendances WHERE attendance_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, attendanceId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return new Attendance(
                            rs.getInt("attendance_id"),
                            rs.getString("emp_id"),
                            "", //
                            rs.getDate("record_date"),
                            rs.getString("status"),
                            rs.getString("leave_reason")
                    );
                }
            }
        }
        return null;
    }

    public List<Attendance> getAttendancesByEmployee(String empId) throws SQLException {
        List<Attendance> attendances = new ArrayList<>();
        String sql = "SELECT a.*, e.name AS emp_name FROM attendances a " +
                "JOIN employees e ON a.emp_id = e.emp_id " +
                "WHERE a.emp_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, empId);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    attendances.add(new Attendance(
                            rs.getInt("attendance_id"),
                            rs.getString("emp_id"),
                            "",
                            rs.getDate("record_date"),
                            rs.getString("status"),
                            rs.getString("leave_reason")
                    ));
                }
            }
        }
        return attendances;
    }

    public List<Attendance> getAllAttendances() throws SQLException {
        List<Attendance> attendances = new ArrayList<>();
        String sql = "SELECT * FROM attendances";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                attendances.add(new Attendance(
                        rs.getInt("attendance_id"),
                        rs.getString("emp_id"),
                        rs.getDate("record_date"),
                        rs.getString("status"),
                        rs.getString("leave_reason")
                ));
            }
        }
        return attendances;
    }

    public List<Attendance> searchAttendances(String empId, String name, String status,
                                              Date startDate, Date endDate) throws SQLException {
        List<Attendance> attendances = new ArrayList<>();

        StringBuilder sql = new StringBuilder(
                "SELECT a.*, e.name AS emp_name FROM attendances a " +
                        "JOIN employees e ON a.emp_id = e.emp_id " +
                        "WHERE 1=1"
        );

        List<Object> params = new ArrayList<>();

        if (empId != null && !empId.isEmpty()) {
            sql.append(" AND a.emp_id = ?");
            params.add(empId);
        }

        if (name != null && !name.isEmpty()) {
            sql.append(" AND e.name LIKE ?");
            params.add("%" + name + "%");
        }

        if (status != null && !status.isEmpty()) {
            sql.append(" AND a.status = ?");
            params.add(status);
        }

        if (startDate != null) {
            sql.append(" AND a.record_date >= ?");
            params.add(new java.sql.Date(startDate.getTime()));
        }

        if (endDate != null) {
            sql.append(" AND a.record_date <= ?");
            params.add(new java.sql.Date(endDate.getTime()));
        }

        sql.append(" ORDER BY a.record_date DESC");

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Attendance att = new Attendance();
                    att.setAttendanceId(rs.getInt("attendance_id"));
                    att.setEmpId(rs.getString("emp_id"));
                    att.setEmpName(rs.getString("emp_name"));
                    att.setRecordDate(rs.getDate("record_date"));
                    att.setStatus(rs.getString("status"));
                    att.setLeaveReason(rs.getString("leave_reason"));
                    attendances.add(att);
                }
            }
        }
        return attendances;
    }
}